﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


using PKURG.CostSplit.SyncManual.DAL;
using PKURG.CostSplit.SyncManual.Properties;
using PKURG.CostSplit.SyncManual.ERP;

namespace PKURG.CostSplit.SyncService.ERP
{
    public class SyncData
    {
        #region queryBaseString
        static string queryBaseString = @"SELECT   pt.task_id         期ID
	                    ,task_name       期名称
                           ,ppa.project_id      项目ID
                           ,ppa.name            项目名称
                           ,''            项目简称
                           ,ffv_co.DESCRIPTION  项目公司   
                          ,substr(hou.name,instr(hou.name,'_')+1,LENGTH(hou.name)-9) 项目公司简称 
                          ,''                                     城市公司
                          ,''                                     城市公司简称
                          ,'资源集团'          产业集团
                          ,sysdate             创建时间
                      FROM pa.PA_TASKS  PT   
                          ,pa.pa_projects_all ppa
                          ,apps.hr_operating_units  hou
                          ,gl.gl_ledgers  gl
                          ,apps.FND_ID_FLEX_SEGMENTS_VL  fifs_co
                          ,apps.fnd_flex_values_vl   ffv_co
                     WHERE PT.CHARGEABLE_FLAG = 'Y'
                       --and pt.start_date
	                     and sysdate <= Nvl(pt.completion_date,sysdate-1)
                       and pt.project_id = ppa.project_id
                       and ppa.template_flag = 'N'
                       and ppa.project_status_code = 'APPROVED'
                       and hou.organization_id = ppa.org_id
                       and hou.set_of_books_id = gl.ledger_id
                       and FIFS_CO.APPLICATION_ID = 101
                       AND FIFS_CO.ID_FLEX_CODE = 'GL#'
                       AND FIFS_CO.ID_FLEX_NUM  = gl.chart_of_accounts_id
                       AND FIFS_CO.APPLICATION_COLUMN_NAME = 'SEGMENT1'
                       --and ffv.FLEX_VALUE=pt.attribute9
                       AND FFV_CO.FLEX_VALUE_SET_ID = FIFS_CO.FLEX_VALUE_SET_ID
                       AND FFV_CO.FLEX_VALUE        = hou.short_code
                     group by pt.task_id
                             ,ppa.project_id
                             ,ppa.name
                             ,ffv_co.DESCRIPTION
                             ,hou.name
                             ,task_name";

        #endregion

        #region queryDetailString
        //        string queryDetailString = @"select project_id      --项目ID
        //                            ,task_id         --期ID
        //                            ,expenditure_type   --科目名称
        //                            ,contract_amt               --合同金额
        //                            ,sj_amount                  --设计变更金额
        //                            ,qs_amount                  --签证洽商金额
        //                            ,other_amt                  --其它结算事项金额
        //
        //                            ,contract_plan_amt          --合约规划限额
        //
        //                            ,addition_amt               --补充协议金额
        //                            ,qz_amount                  --现场签证金额
        //                            ,estimat_amount             --预计待发生金额
        //                            from cux_pa_dynamic_cost_bi_t";

        #endregion

        #region queryDetailString
        string queryDetailString = @"select A.project_id      --项目ID
                                    ,A.task_id         --期ID
                                    ,A.expenditure_type   --科目名称
                                    ,contract_amt               --合同金额
                                    ,sj_amount                  --设计变更金额
                                    ,qs_amount                  --签证洽商金额
                                    ,other_amt                  --其它结算事项金额

                                    ,contract_plan_amt          --合约规划限额

                                    ,addition_amt               --补充协议金额
                                    ,qz_amount                  --现场签证金额
                                    ,estimat_amount             --预计待发生金额
                                    ,case B.ADJ_AMT when null then 0 else B.ADJ_AMT end ADJ_AMT
                                    from cux_pa_dynamic_cost_bi_t A
                                    left join 
                                    (
                                    select cpea.project_id
                                          ,cpea.task_id
                                          ,pet.description  expenditure_type
                                          ,nvl(sum(cpea.estimate_amount),0)  ADJ_AMT
                                      from cux_po_estimate_amount_all cpea
                                          ,pa_expenditure_types  pet
                                     where cpea.status = '已提交'
                                       and cpea.expenditure_type = pet.description
                                     group by cpea.project_id
                                             ,cpea.task_id
                                             ,pet.description
                                    )   B
                                    on A.TASK_ID=B.task_id and A.EXPENDITURE_TYPE=B.expenditure_type";

        #endregion

        #region queryContractDetailString
        string queryContractDetailString = @"select PROJECT_ID,
  TASK_ID,
  EXPENDITURE_TYPE,
  CONTRACT_NUMBER,
  CONTRACT_NAME,
  CONTRACT_PLAN_AMT,
  CONTRACT_AMT,
  SUPP_AMT ,
  QZ_AMOUNT,
  QS_AMOUNT,
  SJ_AMOUNT,
  OTHER_AMT,
  ESTIMAT_AMOUNT,
  JS_AMOUNT,
  CONTRACT_RANGE,
CONTRACT_RANGE  CONTRACT_RANGE2  from apps.CUX_PA_DYNAMIC_COST_CONTRACT_T ";

        #endregion

        private static DataSet GetBaseData(string sql)
        {
            Founder.PKURG.DBUntility.DbHelperMSOra.connectionString = Settings.Default.ERPConnectonString;
            DataSet ds = Founder.PKURG.DBUntility.DbHelperMSOra.Query(sql);
            return ds;
        }

        #region 暂不用
        //internal ReturnInfo SyncCityData()
        //{
        //    Exception exeException = null;
        //    try
        //    {
        //        string sql = string.Format("select 项目公司简称 from ({0}) A group by 项目公司简称", queryBaseString);
        //        DataSet ds = GetBaseData(sql);
        //        if (ds == null || ds.Tables.Count <= 0)
        //        {
        //            return new ReturnInfo()
        //            {
        //                ex = new Exception("返回数据集不正确")
        //            };
        //        }
        //        foreach (DataRow item in ds.Tables[0].Rows)
        //        {
        //            string companyName = item["项目公司简称"].ToString();
        //            Company company = context.Company.Where(x => x.CompanyName == companyName).FirstOrDefault();
        //            if (company == null)
        //            {
        //                company = new Company()
        //                {
        //                    CompanyName = companyName,
        //                    Id = new Guid()
        //                };
        //                context.Company.AddObject(company);
        //            }
        //        }
        //        context.SaveChanges();

        //    }
        //    catch (Exception ex)
        //    {
        //        exeException = ex;
        //    }
        //    return new ReturnInfo()
        //    {
        //        ex = exeException
        //    };
        //}
        //internal ReturnInfo SyncProjectData()
        //{
        //    Exception exeException = null;
        //    try
        //    {
        //        string sql = string.Format("select 项目ID,项目名称,项目公司简称 from ({0}) A group by 项目ID,项目名称,项目公司简称", queryBaseString);
        //        DataSet ds = GetBaseData(sql);
        //        if (ds == null || ds.Tables.Count <= 0)
        //        {
        //            return new ReturnInfo()
        //            {
        //                ex = new Exception("返回数据集不正确")
        //            };
        //        }
        //        foreach (DataRow item in ds.Tables[0].Rows)
        //        {
        //            string companyName = item["项目公司简称"].ToString();
        //            Company company = context.Company.Where(x => x.CompanyName == companyName).FirstOrDefault();
        //            if (company == null)
        //            {
        //                company = new Company()
        //                {
        //                    CompanyName = companyName,
        //                    Id = new Guid()
        //                };
        //                context.Company.AddObject(company);
        //            }
        //        }
        //        context.SaveChanges();

        //    }
        //    catch (Exception ex)
        //    {
        //        exeException = ex;
        //    }
        //    return new ReturnInfo()
        //    {
        //        ex = exeException
        //    };
        //}

        #endregion

        /// <summary>
        /// 同步基础信息（ERP）
        /// </summary>
        /// <returns></returns>
        internal ReturnInfo SyncBaseData()
        {
            CostSplitEntities context = new CostSplitEntities();
            Exception exeException = null;
            try
            {
                DataSet ds = GetBaseData(queryBaseString);
                if (ds == null || ds.Tables.Count <= 0)
                {
                    return new ReturnInfo()
                    {
                        ex = new Exception("返回数据集不正确")
                    };
                }
                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    string companyName = item["项目公司简称"].ToString();
                    Company company = context.Company.Where(x => x.CompanyName == companyName).FirstOrDefault();
                    if (company == null)
                    {
                        company = new Company()
                        {
                            CompanyName = companyName,
                            Id = Guid.NewGuid()
                        };
                        context.Company.AddObject(company);
                    }

                    int projectID = int.Parse(item["项目ID"].ToString());
                    string projectName = item["项目名称"].ToString();
                    EProject project = context.EProject.Where(x => x.Id == projectID).FirstOrDefault();
                    if (project == null)
                    {
                        project = new EProject()
                        {
                            ProjectName = projectName,
                            Id = int.Parse(item["项目ID"].ToString()),
                            CompanyId = company.Id
                        };
                        context.EProject.AddObject(project);
                    }
                    else if (projectName != project.ProjectName)
                    {
                        project.CompanyId = company.Id;
                        project.ProjectName = projectName;//更新项目名称
                    }


                    string projectStageName = item["期名称"].ToString();
                    int projectStageId = int.Parse(item["期ID"].ToString());
                    ProjectStage projectstage = context.ProjectStage.Where(x => x.Id == projectStageId).FirstOrDefault();
                    if (projectstage == null)
                    {
                        projectstage = new ProjectStage()
                        {
                            Id = projectStageId,
                            StageName = projectStageName,
                            ProjectId = projectID
                        };
                        context.ProjectStage.AddObject(projectstage);
                    }
                    else
                    {
                        projectstage.ProjectId = projectID;
                        projectstage.StageName = projectStageName;
                    }

                    context.SaveChanges();
                }


            }
            catch (Exception ex)
            {
                exeException = ex;
            }
            return new ReturnInfo()
            {
                ex = exeException
            };
        }

        /// <summary>
        /// 同步ERP的成本数据
        /// </summary>
        /// <returns></returns>
        //internal ReturnInfo SyncDetailData()
        //{
        //    CostSplitEntities context = new CostSplitEntities();
        //    Exception exeException = null;
        //    try
        //    {
        //        DataSet ds = GetBaseData(queryDetailString);
        //        if (ds == null || ds.Tables.Count <= 0)
        //        {
        //            return new ReturnInfo()
        //            {
        //                ex = new Exception("返回数据集不正确")
        //            };
        //        }
        //        foreach (DataRow item in ds.Tables[0].Rows)
        //        {
        //            string taskIdString = item["task_id"].ToString();
        //            string expenditure_type = item["expenditure_type"].ToString();
        //            if (string.IsNullOrEmpty(taskIdString) || string.IsNullOrEmpty(expenditure_type))
        //            {
        //                continue;
        //            }
        //            int stageId = 0;
        //            bool isInt = int.TryParse(taskIdString, out stageId);
        //            if (!isInt)
        //            {
        //                continue;
        //            }

        //            TotalCost costItem = context.TotalCost.Where(x => x.AccountName == expenditure_type && x.StageId == stageId).FirstOrDefault();
        //            if (costItem == null)
        //            {
        //                costItem = new TotalCost()
        //                {
        //                    Id = Guid.NewGuid(),
        //                    StageId = stageId,
        //                    AccountName = expenditure_type,
        //                    addition_amt = GetDecData(item["addition_amt"]),
        //                    contract_amt = GetDecData(item["contract_amt"]),
        //                    contract_plan_amt = GetDecData(item["contract_plan_amt"]),
        //                    estimat_amount = GetDecData(item["estimat_amount"]),
        //                    other_amt = GetDecData(item["other_amt"]),
        //                    qs_amount = GetDecData(item["qz_amount"]),
        //                    qz_amount = GetDecData(item["addition_amt"]),
        //                    sj_amount = GetDecData(item["sj_amount"]),
        //                     ADJ_AMT=GetDecData(item["ADJ_AMT"])
        //                };
        //                context.TotalCost.AddObject(costItem);
        //            }
        //            else
        //            {
        //                costItem.addition_amt = GetDecData(item["addition_amt"]);
        //                costItem.contract_amt = GetDecData(item["contract_amt"]);
        //                costItem.contract_plan_amt = GetDecData(item["contract_plan_amt"]);
        //                costItem.estimat_amount = GetDecData(item["estimat_amount"]);
        //                costItem.other_amt = GetDecData(item["other_amt"]);
        //                costItem.qs_amount = GetDecData(item["qz_amount"]);
        //                costItem.qz_amount = GetDecData(item["addition_amt"]);
        //                costItem.sj_amount = GetDecData(item["sj_amount"]);
        //                costItem.ADJ_AMT = GetDecData(item["ADJ_AMT"]);
        //            }
        //        }
        //        context.SaveChanges();

        //    }
        //    catch (Exception ex)
        //    {
        //        exeException = ex;
        //    }
        //    return new ReturnInfo()
        //    {
        //        ex = exeException
        //    };
        //}

        private decimal? GetDecData(Object obj)
        {
            if (obj == null)
            {
                return 0;
            }
            if (string.IsNullOrEmpty(obj.ToString()))
            {
                return 0;
            }


            decimal value = 0;
            bool isDec = decimal.TryParse(obj.ToString(), out value);
            if (isDec)
            {
                return value;
            }
            return 0;
        }

        /// <summary>
        /// 同步ERP的成本数据
        /// </summary>
        /// <param name="isOverWrite">是否直接覆盖数据（true直接删除原合约规划和同行数据）</param>
        /// <returns></returns>
        internal ReturnInfo SyncContractDetailData(bool updateHappen, bool updateUnHappen, List<EProject> projectList)
        {
            CostSplitEntities context = new CostSplitEntities();

            Exception exeException = null;
            try
            {
                DataSet ds = GetBaseData(queryContractDetailString);
                if (ds == null || ds.Tables.Count <= 0)
                {
                    return new ReturnInfo()
                    {
                        ex = new Exception("返回数据集不正确")
                    };
                }


                if (updateUnHappen)
                {
                    //开始处理原合约规划和同行数据
                    context.ClearContractCost();
                }

                //清除已删除合同
                bool isProcessDeleteContracts = true;
                if (isProcessDeleteContracts)
                {
                    List<int> pids = projectList.Select(x => x.Id).ToList();
                    List<ProjectStage> stageList = context.ProjectStage.Where(x => pids.Contains(x.ProjectId.Value)).ToList();

                    //获取本次要更新的List
                    List<string> stageIds = stageList.Select(x => x.Id.ToString()).ToList();
                    List<ContractCost> ccList = context.ContractCost.Where(x => stageIds.Contains(x.StageId) && !string.IsNullOrEmpty(x.ContractNum)).ToList();

                    List<DsItemInfos> itemInfos = ConvertToInfo(ds.Tables[0]);
                    foreach (var item in ccList)
                    {
                        if (itemInfos.FindIndex(x => x.CONTRACT_NUMBER == item.ContractNum) == -1)
                        {
                            //ERP中已经不存在该和同行
                            // context.ContractCost.Detach();
                            context.ContractCost.DeleteObject(item);
                        }
                    }
                }



                //foreach (var item in context.ContractCost.ToList())
                //{
                //    ProjectStage stage = context.ProjectStage.Where(x => x.Id.ToString() == item.StageId).FirstOrDefault();
                //    if (stage == null)
                //    {
                //        continue;
                //    }
                //    if (projectList.FindIndex(x => x.Id == stage.ProjectId) == -1)
                //    {
                //        continue;
                //        //不在本次更新列表当中
                //    }

                //}

                foreach (DataRow item in ds.Tables[0].Rows)
                {
                    string taskIdString = item["task_id"].ToString();//期
                    string expenditure_type = item["EXPENDITURE_TYPE"].ToString();
                    string contractId = item["CONTRACT_NUMBER"].ToString();
                    string contractName = item["CONTRACT_NAME"].ToString();

                    string PROJECT_ID = item["PROJECT_ID"].ToString();

                    if (projectList.Where(x => x.Id == int.Parse(PROJECT_ID)).FirstOrDefault() == null)
                    {
                        continue;
                    }

                    if (string.IsNullOrEmpty(taskIdString) || string.IsNullOrEmpty(expenditure_type))
                    {
                        continue;
                    }
                    int stageId = 0;
                    bool isInt = int.TryParse(taskIdString, out stageId);
                    if (!isInt)
                    {
                        continue;
                    }

                    if (string.IsNullOrEmpty(contractId))
                    {
                        if (!updateUnHappen)
                        {
                            continue;
                        }

                        //合约规划数据
                        ContractCost costItem = context.ContractCost.Where(x => x.AccountName == expenditure_type && x.StageId == taskIdString && x.ContractName == contractName && x.ContractNum == "").FirstOrDefault();
                        if (costItem == null)
                        {
                            costItem = new ContractCost()
                            {
                                Id = Guid.NewGuid(),
                                StageId = taskIdString,
                                AccountName = expenditure_type,
                                ContractNum = contractId,
                                ContractName = contractName,

                                ht_amount = GetDecData(item["CONTRACT_AMT"]),
                                qs_amount = GetDecData(item["QZ_AMOUNT"]) + GetDecData(item["QS_AMOUNT"]),
                                sj_amount = GetDecData(item["SJ_AMOUNT"]),
                                js_amount = GetDecData(item["JS_AMOUNT"]),
                                bc_amount = GetDecData(item["SUPP_AMT"]),
                                other_amount = GetDecData(item["OTHER_AMT"]),
                                ht_plan_amount = GetDecData(item["CONTRACT_PLAN_AMT"]),
                                tz_amount = GetDecData(item["ESTIMAT_AMOUNT"]),
                                CreateTime = DateTime.Now,
                                UpdateTime = DateTime.Now,
                                Scope = item["CONTRACT_RANGE"].ToString(),
                                SyncType = 0
                            };
                            context.ContractCost.AddObject(costItem);
                        }
                        else
                        {
                            costItem.ht_amount = GetDecData(item["CONTRACT_AMT"]);
                            costItem.qs_amount = GetDecData(item["QZ_AMOUNT"]) + GetDecData(item["QS_AMOUNT"]);
                            costItem.sj_amount = GetDecData(item["SJ_AMOUNT"]);
                            costItem.js_amount = GetDecData(item["JS_AMOUNT"]);
                            costItem.bc_amount = GetDecData(item["SUPP_AMT"]);
                            costItem.other_amount = GetDecData(item["OTHER_AMT"]);
                            costItem.ht_plan_amount = GetDecData(item["CONTRACT_PLAN_AMT"]);
                            costItem.tz_amount = GetDecData(item["ESTIMAT_AMOUNT"]);
                            costItem.Scope = item["CONTRACT_RANGE"].ToString();
                            costItem.UpdateTime = DateTime.Now;
                        }

                    }
                    else
                    {
                        if (updateHappen)
                        {
                            ContractCost costItem = context.ContractCost.Where(x => x.AccountName == expenditure_type && x.StageId == taskIdString && x.ContractNum == contractId).FirstOrDefault();
                            if (costItem == null)
                            {
                                costItem = new ContractCost()
                                {
                                    Id = Guid.NewGuid(),
                                    StageId = taskIdString,
                                    AccountName = expenditure_type,
                                    ContractNum = contractId,
                                    ContractName = contractName,

                                    ht_amount = GetDecData(item["CONTRACT_AMT"]),
                                    qs_amount = GetDecData(item["QZ_AMOUNT"]) + GetDecData(item["QS_AMOUNT"]),
                                    sj_amount = GetDecData(item["SJ_AMOUNT"]),
                                    js_amount = GetDecData(item["JS_AMOUNT"]),
                                    bc_amount = GetDecData(item["SUPP_AMT"]),
                                    other_amount = GetDecData(item["OTHER_AMT"]),

                                    ht_plan_amount = GetDecData(item["CONTRACT_PLAN_AMT"]),
                                    tz_amount = GetDecData(item["ESTIMAT_AMOUNT"]),

                                    Scope = item["CONTRACT_RANGE"].ToString(),
                                    CreateTime = DateTime.Now,
                                    UpdateTime = DateTime.Now,
                                    SyncType = 1//首次同步
                                };
                                context.ContractCost.AddObject(costItem);
                            }
                            else
                            {
                                if ((GetDecData(item["JS_AMOUNT"]) != 0 || costItem.js_amount!=0) && GetDecData(item["JS_AMOUNT"]) != costItem.js_amount)
                                {
                                    costItem.SyncType = 1;//发生变化
                                }
                                else
                                {
                                    decimal? oldValue = costItem.ht_amount + costItem.qs_amount + costItem.sj_amount +  costItem.bc_amount + costItem.other_amount;

                                    decimal? newValue = GetDecData(item["CONTRACT_AMT"]) + GetDecData(item["QZ_AMOUNT"]) + GetDecData(item["QS_AMOUNT"]) + GetDecData(item["SJ_AMOUNT"])
                                        + GetDecData(item["SUPP_AMT"]) + GetDecData(item["OTHER_AMT"]);

                                    if (oldValue == newValue)
                                    {
                                        costItem.SyncType = 0;//没有发生变化
                                    }
                                    else
                                    {
                                        costItem.SyncType = 1;//发生变化
                                    }
                                }
                                costItem.ContractName = contractName;
                                costItem.ht_amount = GetDecData(item["CONTRACT_AMT"]);//合同金额
                                costItem.qs_amount = GetDecData(item["QZ_AMOUNT"]) + GetDecData(item["QS_AMOUNT"]);//工程洽商\签证金额
                                costItem.sj_amount = GetDecData(item["SJ_AMOUNT"]);//设计变更
                                costItem.js_amount = GetDecData(item["JS_AMOUNT"]);//结算金额
                                costItem.bc_amount = GetDecData(item["SUPP_AMT"]);//补充协议金额
                                costItem.other_amount = GetDecData(item["OTHER_AMT"]);//其他

                                costItem.ht_plan_amount = GetDecData(item["CONTRACT_PLAN_AMT"]);
                                costItem.tz_amount = GetDecData(item["ESTIMAT_AMOUNT"]);
                                costItem.Scope = item["CONTRACT_RANGE"].ToString();

                                costItem.UpdateTime = DateTime.Now;



                            }
                        }
                    }
                }

                context.SaveChanges();

            }
            catch (Exception ex)
            {
                exeException = ex;
            }
            return new ReturnInfo()
            {
                ex = exeException
            };
        }

        private List<DsItemInfos> ConvertToInfo(DataTable dataTable)
        {
            List<DsItemInfos> infos = new List<DsItemInfos>();
            foreach (DataRow item in dataTable.Rows)
            {
                if (string.IsNullOrEmpty(item["CONTRACT_NUMBER"].ToString()))
                {
                    continue;
                }
                infos.Add(new DsItemInfos()
                {
                    CONTRACT_NUMBER = item["CONTRACT_NUMBER"].ToString()
                });
            }
            return infos;
        }

    }
}
